package jdbc;

import org.junit.Test;

import javax.sql.rowset.serial.SerialBlob;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestTranscation {

    @Test
    public void testTranscation() {
        Connection conn = null;
        PreparedStatement pstmt = null;

        String sql1 = "UPDATE account SET money = money - 100 WHERE id = 1";
        String sql2 = "UPDATE account SET money = money + 100 WHERE id = 2";

        try {
            conn = ConnUtil.getConnection();
            conn.setAutoCommit(false);

            pstmt = conn.prepareStatement(sql1);
            pstmt.executeUpdate();

            pstmt = conn.prepareStatement(sql2);
            pstmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("事务回滚");
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            System.out.println("提交事务");
            try {
                conn.commit();
                ConnUtil.close(null, pstmt, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



    @Test
    public void testReadUnCommitted() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        String sql = "SELECT * FROM account";
        try {
            conn = ConnUtil.getConnection();
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            pstmt = conn.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                Map map = new HashMap();
                map.put("id", resultSet.getInt("id"));
                map.put("money", resultSet.getDouble("money"));
                System.out.println(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnUtil.close(resultSet, pstmt, conn);
        }
    }


    @Test
    public void testSavePoint() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        Savepoint savepoint = null;
        String sql1 = "UPDATE account SET money = money - 50 WHERE id = 1";
        String sql2 = "UPDATE account SET money = money + 50 WHERE id = 2";
        String sql3 = "UPDATE account SET money = money - 50 WHERE id = 1";
        String sql4 = "UPDATE account SET money = money + 50 WHERE id = 2";

        try {
            conn = ConnUtil.getConnection();
            conn.setAutoCommit(false);

            pstmt = conn.prepareStatement(sql1);
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement(sql2);
            pstmt.executeUpdate();

            savepoint = conn.setSavepoint();

            pstmt = conn.prepareStatement(sql3);
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement(sql4);
            pstmt.executeUpdate();
            int i = 1/0;
        }
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("事务回滚到指定点");
            try {
                conn.rollback(savepoint);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        finally {
            System.out.println("提交事务");
            try {
                conn.commit();
                ConnUtil.close(null, pstmt, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}



